package cn.edu.klmy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import cn.edu.klmy.dao.IClaszDao;
import cn.edu.klmy.models.clasz.Clasz;

@Repository
public class ClaszDao extends JdbcDaoSupport implements IClaszDao {

    private static final String ADD_CLASZ = "INSERT INTO CLASS(CLASS_NAME, CLASS_YEAR, CLASS_TYPE) VALUES(?, ?, ?)";

    @Override
    public void addClasz(Clasz clasz) {
        getJdbcTemplate().update(ADD_CLASZ, new Object[]{
                clasz.getName(),
                clasz.getCreateYear(),
                clasz.getType()
        });
    }

    private static final String REMOVE_CLASZ = "DELETE FROM CLASS WHERE ID = ?";

    @Override
    public void removeClasz(int claszId) {
        getJdbcTemplate().update(REMOVE_CLASZ, new Object[]{claszId});
    }

    private static final String UPDATE_CLASZ = "UPDATE class SET CLASS_NAME = ?, CLASS_YEAR =?, CLASS_TYPE = ? WHERE id = ?";

    @Override
    public void updateClasz(Clasz clasz) {
        getJdbcTemplate().update(UPDATE_CLASZ,  new Object[]{
                clasz.getName(),
                clasz.getCreateYear(),
                clasz.getType(),
                clasz.getId()
        });
    }

    public static final String GET_CLASZ = "SELECT * FROM class WHERE ";

    @Override
    public List<Clasz> getClasz(String fieldName, Object value) {
        if (fieldName == null)
            return null;
        String sql = GET_CLASZ + fieldName + "=" + value;
        return (List<Clasz>) getJdbcTemplate().query(sql, new Object[]{}, new ClaszRowMapper());
    }

    private static final String CLASZ_EXISTS = "SELECT COUNT(*) FROM class WHERE CLASS_NAME = ?";

    @Override
    public boolean claszExists(String claszName) {
        int result = getJdbcTemplate().queryForInt(CLASZ_EXISTS, new Object[]{claszName});

        if (result < 1) {
            return false;
        }
        return true;
    }

	@Override
    public List<Clasz> getClaszs(Map<String, Object[]> filter, int pageNum, int pageSize) {
        String sql = DaoUtil.getFuzzyQueryPageSql("CLASS", filter, pageNum, pageSize);
        return (List<Clasz>) getJdbcTemplate().query(sql, new Object[]{}, new ClaszRowMapper());
    }

    @Override
    public int getClaszCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("CLASS", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    private static final String GET_CLASZS_OF_TEACHER = "SELECT ID, CLASS_NAME, CLASS_YEAR, CLASS_TYPE FROM class WHERE ID IN (SELECT CLASS_ID FROM TEACHER_CLASSES WHERE TEACHER_ID = ?)";

    @Override
    public List<Clasz> getClaszsOfTeacher(int teacherId) {
        return (List<Clasz>) getJdbcTemplate().query(GET_CLASZS_OF_TEACHER, new Object[]{ teacherId }, new ClaszRowMapper());
    }

    private static final String GET_CLASZS_NOT_OF_TEACHER = "SELECT ID, CLASS_NAME, CLASS_YEAR, CLASS_TYPE FROM class WHERE ID NOT IN (SELECT CLASS_ID FROM TEACHER_CLASSES WHERE TEACHER_ID = ?)";
    @Override
    public List<Clasz> getClaszsNotOfTeacher(int teacherId) {
        return (List<Clasz>) getJdbcTemplate().query(GET_CLASZS_NOT_OF_TEACHER, new Object[]{ teacherId }, new ClaszRowMapper());
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }
    private static class ClaszRowMapper implements RowMapper<Clasz> {

            @Override
            public Clasz mapRow(ResultSet resultSet, int i) throws SQLException {
                Clasz clasz = new Clasz();
                clasz.setId(resultSet.getInt("id"));
                clasz.setName(resultSet.getString("CLASS_NAME"));
                clasz.setCreateYear(resultSet.getString("CLASS_YEAR"));
                clasz.setType(resultSet.getInt("CLASS_TYPE"));
                return clasz;
            }
        }
}
